﻿using AutoMapper;
using DeZhen.DataCollection.IRepository.CollectionInstance;
using DeZhen.DataCollection.IRepository.CollectionTemplate;
using DeZhen.DataCollection.IService.CollectionTemplate;
using DeZhen.DataCollection.Model.DTO.CollectionTemplate;
using DeZhen.DataCollection.Model.DTO.DataSourceSettings;
using DeZhen.DataCollection.Model.Entities;
using DeZhen.DataCollection.Model.Enums;
using SqlSugar;
using System.Threading.Tasks;
using Yitter.IdGenerator;
using static System.Runtime.InteropServices.JavaScript.JSType;

namespace DeZhen.DataCollection.Service.CollectionTemplate
{
    public class CollectionTemplateService : ICollectionTemplateService
    {
        private readonly IMapper _mapper;
        private readonly ISqlSugarClient _sqlSugarClient;
        private readonly ICollectionTemplateRepository _repository;
        private readonly ICollectionTemplateDetailRepository _detailRepository;
        private readonly ICollectionInstanceRepository _instanceRepository;
        private readonly ICollectionInstanceDetailRepository _instanceDetailRepository;
        public CollectionTemplateService(IMapper mapper,
                                        ICollectionTemplateRepository repository,
                                        ICollectionTemplateDetailRepository detailRepository,
                                        ICollectionInstanceDetailRepository instanceDetailRepository,
                                        ICollectionInstanceRepository instanceRepository,
                                        ISqlSugarClient sqlSugarClient)
        {
            _repository = repository;
            _mapper = mapper;
            _detailRepository = detailRepository;
            _instanceDetailRepository = instanceDetailRepository;
            _instanceRepository = instanceRepository;
            _sqlSugarClient = sqlSugarClient;
        }

        public async Task<List<CollectionTemplateDropDownDTO>> GetDropDownListAsync()
        {
            var modelList = await _repository.GetListAsync();

            return _mapper.Map<List<CollectionTemplateDropDownDTO>>(modelList);
        }

        public async Task<List<TemplateParamSettingDTO>> GetTemplateParamSettingAsync(CollectionTypeEnum collectionType, long? templateId)
        {
            var templates = (await _repository.GetListAsync()).WhereIF(templateId > 0, x => x.Id == templateId);
            if (templates == null || !templates.Any())
                return [];

            var templateIds = templates.Select(t => t.Id).ToList();
            var allDetails = new List<CollectionTemplateDetail>();
            foreach (var id in templateIds)
            {
                var details = await _detailRepository.GetDetailByTemplateIdAsync(id);
                if (details != null && details.Any())
                    allDetails.AddRange(details);
            }

            var detailGroup = allDetails.GroupBy(d => d.CollectionTemplateId)
                                        .ToDictionary(g => g.Key, g => g.ToList());

            var result = new List<TemplateParamSettingDTO>();

            foreach (var template in templates)
            {
                detailGroup.TryGetValue(template.Id, out var detailList);
                detailList ??= new List<CollectionTemplateDetail>();

                var personList = detailList.Where(d => (int)d.ColumnType == (int)collectionType).ToList();
                var dateList = detailList.Where(d => d.ColumnType == BusinessColumnType.DateColumn).ToList();

                // 组装单个 BusinessPersonDTO
                BusinessPersonDTO businessPerson = null;
                if (personList.Any())
                {
                    var checkedPersonId = await GetCheckedId(collectionType, template.Id, (BusinessColumnType)((int)collectionType));
                    businessPerson = new BusinessPersonDTO
                    {
                        CheckedId = checkedPersonId,
                        CollectionPersonInfo = personList.Select(p => new CollectionColumnsDTO
                        {
                            ColumnsId = p.Id,
                            ColumnsName = p.ColumnName,
                            ColumnCnName = p.ColumnCnName
                        }).ToList()
                    };
                }

                // 组装单个 BusinessTimeDTO
                BusinessTimeDTO businessDate = null;
                if (dateList.Any())
                {
                    var checkedDateId = await GetCheckedId(collectionType, template.Id, BusinessColumnType.DateColumn);
                    businessDate = new BusinessTimeDTO
                    {
                        CheckedId = checkedDateId,
                        CollectionDateInfo = dateList.Select(d => new CollectionColumnsDTO
                        {
                            ColumnsId = d.Id,
                            ColumnsName = d.ColumnName,
                            ColumnCnName = d.ColumnCnName
                        }).ToList()
                    };
                }

                var templateParamSetting = new TemplateParamSettingDTO
                {
                    TemplateId = template.Id,
                    TableName = template.TableName,
                    TableCnName = template.TableCnName,
                    BusinessPersonList = businessPerson,
                    BusinessDateList = businessDate
                };

                result.Add(templateParamSetting);
            }

            return result;
        }

        public async Task<bool> SaveTemplateParamSettingAsync(SaveTemplateParamSetting input)
        {
            if (input == null || input.TemplateParamSettings == null || !input.TemplateParamSettings.Any())
                return false;

            // 事务处理，确保数据一致性
            var success = true;
            var now = DateTime.Now;

            // 1. 新建采集实例
            var instanceId = YitIdHelper.NextId();
            var instanceName = input.CollectionType == CollectionTypeEnum.ByPatient
                ? $"按患者采集实例_{now:yyyyMMddHHmmssfff}"
                : $"按医务人员采集实例_{now:yyyyMMddHHmmssfff}";

            var instanceModel = new CollectionInstances
            {
                Id = instanceId,
                InstanceName = instanceName,
                CollectionType = input.CollectionType,
                CreateTime = now,
                UpdateTime = now
            };


            // 2. 新建采集实例明细
            var details = new List<CollectionInstanceDetail>();

            foreach (var item in input.TemplateParamSettings)
            {
                // 2.1 处理业务人员字段
                if (item.BusinessPersonList?.CheckedId != null)
                {
                    details.Add(new CollectionInstanceDetail
                    {
                        Id = YitIdHelper.NextId(),
                        CollectionInstanceId = instanceId,
                        CollectionTemplateId = item.TemplateId,
                        CollectionTemplateDetailId = item.BusinessPersonList.CheckedId.Value,
                        TableName = item.TableName,
                        TableCnName = item.TableCnName,
                        ColumnType = (BusinessColumnType)(int)input.CollectionType, // 这里按采集类型映射
                        QueryCondition = null,
                        ExecuteSql = null,
                        CreateTime = now
                    });
                }

                // 2.2 处理业务日期字段
                if (item.BusinessDateList?.CheckedId != null)
                {
                    details.Add(new CollectionInstanceDetail
                    {
                        Id = YitIdHelper.NextId(),
                        CollectionInstanceId = instanceId,
                        CollectionTemplateId = item.TemplateId,
                        CollectionTemplateDetailId = item.BusinessDateList.CheckedId.Value,
                        TableName = item.TableName,
                        TableCnName = item.TableCnName,
                        ColumnType = BusinessColumnType.DateColumn,
                        QueryCondition = null,
                        ExecuteSql = null,
                        CreateTime = now
                    });
                }
            }


            // 3. 使用事务提交
            var result = await _sqlSugarClient.Ado.UseTranAsync(async () =>
            {
                await _instanceRepository.CreateAsync(instanceModel);
                await _instanceDetailRepository.CreateManyAsync(details);
            });

            return success;
        }

        public async Task<bool> ExistsTemplateParamSetting(QueryTemplateParamSetting param)
        {
            if (param == null)
                return false;

            var instance = await _instanceRepository.GetLatestInstanceAsync(param.CollectionType);
            return instance != null;
        }

        public async Task<List<PreviewSqlDTO>> GetPreviewSqlListAsync(QueryTemplateParamSetting param)
        {
            var resultList = new List<PreviewSqlDTO>();
            if (param == null)
                return resultList;

            var instance = await _instanceRepository.GetLatestInstanceAsync(param.CollectionType);
            if (instance == null)
                return resultList;

            var instanceDetails = await _instanceDetailRepository.GetDetailByInstanceIdAsync(instance.Id);
            if (instanceDetails == null || !instanceDetails.Any())
                return resultList;

            bool hasIDCard = !string.IsNullOrWhiteSpace(param.IDCardList);
            bool hasDate = param.BeginDate != null && param.EndDate != null;

            // 1. 按 CollectionTemplateId 分组
            var groupList = instanceDetails
                            .Where(x => x.CollectionTemplateId.HasValue)
                            .GroupBy(x => x.CollectionTemplateId.Value)
                            .ToList();

            // 2. 遍历分组
            foreach (var group in groupList)
            {
                var collectionTemplateId = group.Key;
                var templateModel = await _repository.GetByIdAsync(collectionTemplateId);
                // 3. 查询该 CollectionTemplateId 下的所有明细
                var detailList = (await _instanceDetailRepository.GetListAsync())
                                .Where(x => x.CollectionInstanceId == instance.Id)
                                .Where(x => x.CollectionTemplateId == collectionTemplateId)
                                .ToList();

                string sqlWhere = "";
                var whereList = new List<string>();
                foreach (var item in detailList)
                {
                    var detailModel = await _detailRepository.GetByIdAsync(item.CollectionTemplateDetailId.Value);

                    if (item.ColumnType == BusinessColumnType.DateColumn)
                    {
                        // 4.1 日期条件
                        if (hasDate)
                        {
                            whereList.Add($"{detailModel.ColumnName} between '{param.BeginDate:yyyy-MM-dd}' and '{param.EndDate:yyyy-MM-dd}'");
                        }
                    }
                    else
                    {
                        // 4.2 身份证条件
                        if (hasIDCard)
                        {
                            var idCards = param.IDCardList.Split(',', StringSplitOptions.RemoveEmptyEntries)
                                                     .Select(x => $"'{x.Trim()}'");
                            whereList.Add($"{detailModel.ColumnName} in ({string.Join(",", idCards)})");
                        }
                    }

                    sqlWhere = whereList.Any() ? " where " + string.Join(" and ", whereList) : string.Empty;
                }

                resultList.Add(new PreviewSqlDTO
                {
                    Description = $"源表：{templateModel.TableName} 中文名称：{templateModel.TableCnName}",
                    Sql = $"select * from {templateModel.TableName}{sqlWhere}"
                });
            }

            return resultList;
        }

        public async Task<bool> SaveCollectionTemplateAsync(CollectionTemplateAdd input)
        {
            if (input == null || string.IsNullOrWhiteSpace(input.TableName) || string.IsNullOrWhiteSpace(input.TableCnName) || input.DetailList == null || !input.DetailList.Any())
                return false;

            var now = DateTime.Now;
            var templateId = YitIdHelper.NextId();

            // 1. 检查业务表是否有数据
            var tableName = input.TableName.Trim();
            // 注意：此处未做表名注入防护，实际生产环境建议校验表名合法性
            var templateList = await _repository.GetListAsync(x => x.TableName == input.TableName);
            if (templateList != null && templateList.Any())
            {
                throw new Exception($"业务表[{tableName}]已存在，请勿重复添加。");
            }

            // 2. 构建主表实体
            var templateEntity = new CollectionTemplates
            {
                Id = templateId,
                TableName = input.TableName,
                TableCnName = input.TableCnName,
                CreateTime = now,
                UpdateTime = now
            };

            // 3. 构建明细表实体列表
            var detailEntities = input.DetailList.Select(detail => new CollectionTemplateDetail
            {
                Id = YitIdHelper.NextId(),
                CollectionTemplateId = templateId,
                ColumnName = detail.ColumnName,
                ColumnCnName = detail.ColumnCnName,
                ColumnType = detail.ColumnType,
                CreateTime = now,
                UpdateTime = now
            }).ToList();

            // 4. 事务保存
            var result = await _sqlSugarClient.Ado.UseTranAsync(async () =>
            {
                await _repository.CreateAsync(templateEntity);
                await _detailRepository.CreateManyAsync(detailEntities);
            });

            return result.IsSuccess;
        }

        public async Task<bool> DeleteCollectionTemplateAsync(long templateId)
        {
            if (templateId <= 0)
                return false;
            // 1. 获取模板实体
            var template = await _repository.GetByIdAsync(templateId);
            if (template == null)
                return false;
            // 2. 获取对应的明细列表
            var details = await _detailRepository.GetDetailByTemplateIdAsync(templateId);
            if (details == null || !details.Any())
                return false;
            // 3. 使用事务删除
            var result = await _sqlSugarClient.Ado.UseTranAsync(async () =>
            {
                await _detailRepository.DeleteManyAsync(details);
                await _repository.DeleteAsync(templateId);
            });
            return result.IsSuccess;
        }

        private async Task<long?> GetCheckedId(CollectionTypeEnum collectionType, long collectionTemplateId, BusinessColumnType columnType)
        {
            var instanceModel = await _instanceRepository.GetLatestInstanceAsync(collectionType);

            if (instanceModel != null)
            {
                var templateDetailId = (await _instanceDetailRepository.GetDetailByInstanceIdAsync(instanceModel.Id))
                                        .Where(x => x.CollectionTemplateId == collectionTemplateId)
                                        .Where(x => x.ColumnType == columnType)
                                        .FirstOrDefault()?
                                        .CollectionTemplateDetailId;
                return templateDetailId;
            }
            return null;
        }
    }
}
